package com.hellogood.yaohuo.server.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import org.apache.log4j.Logger;

import com.hellogood.yaohuo.server.db.ConnectionPool;
import com.hellogood.yaohuo.server.db.DBHelper;
import com.hellogood.yaohuo.shared.entity.PsForm;
import com.hellogood.yaohuo.shared.entity.Quyuhuizong;
import com.hellogood.yaohuo.shared.exception.DatabaseException;

public class QuyuhuizongDao extends DBHelper {

	Logger log = Logger.getLogger(this.getClass());

	@Override
	public List<?> initializeObject(ResultSet rs) throws SQLException {

		List<Quyuhuizong> list = new ArrayList<Quyuhuizong>();
		if (rs != null) {
			while (rs.next()) {
				Quyuhuizong hz = new Quyuhuizong();
				hz.setBillNo(rs.getString("billNo"));
				hz.setOrgCode(rs.getString("orgCode"));
				hz.setPluCode(rs.getString("pluCode"));
				hz.setColor(rs.getString("color"));
				hz.setSize(rs.getString("cSize"));
				hz.setCup(rs.getString("cup"));
				hz.setCounts(rs.getInt("counts"));
				hz.setHzDate(rs.getString("hzDate"));
				hz.setIsHz(rs.getString("isHz"));
				list.add(hz);
			}
		}
		return list;
	}

	public String addHuizongBatch(List<Quyuhuizong> list, Set<String> set) {
		String result = "";
		List<String> sqls = new ArrayList<String>();
		for (Quyuhuizong hz : list) {
			String sql = "INSERT INTO quyuHuizong(billNo,orgCode,pluCode,Color,csize,cup,counts,hzDate,isHz) VALUES('" + hz.getBillNo() + "','"
					+ hz.getOrgCode() + "','" + hz.getPluCode() + "','" + hz.getColor() + "','" + hz.getSize() + "','" + hz.getCup() + "',"
					+ hz.getCounts() + ",getDate(),'0')";
			sqls.add(sql);
		}
		for (String bill : set) {
			String sql = "UPDATE yhForm SET status = 1 WHERE YhbillNo = '" + bill + "'";
			sqls.add(sql);
		}
		try {
			executeBatch(sqls);
		} catch (DatabaseException e) {
			e.printStackTrace();
			log.error(e.getMessage());
			return e.getMessage();
		}
		return result;
	}

	public String addUnHuizongBatch(List<Quyuhuizong> list, Set<String> set) {
		String result = "";
		List<String> sqls = new ArrayList<String>();
		for (Quyuhuizong hz : list) {
			String sql = "INSERT INTO quyuHuizong2(billNo,orgCode,pluCode,Color,csize,cup,counts,hzDate,isHz) VALUES('" + hz.getBillNo() + "','"
					+ hz.getOrgCode() + "','" + hz.getPluCode() + "','" + hz.getColor() + "','" + hz.getSize() + "','" + hz.getCup() + "',"
					+ hz.getCounts() + ",getDate(),'0')";
			sqls.add(sql);
		}
		for (String bill : set) {
			String sql = "UPDATE yhForm SET status = 1 WHERE YhbillNo = '" + bill + "'";
			sqls.add(sql);
		}
		try {
			executeBatch(sqls);
		} catch (DatabaseException e) {
			e.printStackTrace();
			log.error(e.getMessage());
			return e.getMessage();
		}
		return result;
	}

	@SuppressWarnings("unchecked")
	public List<Quyuhuizong> getUnHzQuyuhuizong() {
		String sql = "SELECT * FROM quyuHuizong WHERE isHz = 0";
		List<Quyuhuizong> list = (List<Quyuhuizong>) super.query(sql);
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Quyuhuizong> getUnHzQuyuhuizongByStaffCode(String staffCode) {
		String hxDbName = ConnectionPool.hxDbName;
		String sql = "SELECT * FROM quyuHuizong q WHERE q.isHz = 0 AND q.orgCode IN (SELECT orgCode FROM " + hxDbName
				+ "..userorg WHERE staffCode = '" + staffCode + "')";
		List<Quyuhuizong> list = (List<Quyuhuizong>) super.query(sql);
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Quyuhuizong> getUnHzQuyuhuizong2() {
		String sql = "SELECT * FROM quyuHuizong2";
		List<Quyuhuizong> list = (List<Quyuhuizong>) super.query(sql);
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Quyuhuizong> getUnHzQuyuhuizong2ByStaffCode(String staffCode) {
		String hxDbName = ConnectionPool.hxDbName;
		String sql = "SELECT * FROM quyuHuizong2 q AND q.orgCode IN (SELECT orgCode FROM " + hxDbName + "..userorg WHERE staffCode = '" + staffCode
				+ "')";
		List<Quyuhuizong> list = (List<Quyuhuizong>) super.query(sql);
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Quyuhuizong> getUnHzCaigouShenhe() {
		String sql = "SELECT * FROM quyuHuizongShenhe WHERE isHz = 0";
		List<Quyuhuizong> list = (List<Quyuhuizong>) super.query(sql);
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Quyuhuizong> getUnHzCaigouShenheByStaffCode(String staffCode) {
		String hxDbName = ConnectionPool.hxDbName;
		String sql = "SELECT * FROM quyuHuizongShenhe q WHERE q.isHz = 0 AND q.orgCode IN (SELECT orgCode FROM " + hxDbName
				+ "..userorg WHERE staffCode = '" + staffCode + "')";
		List<Quyuhuizong> list = (List<Quyuhuizong>) super.query(sql);
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Quyuhuizong> getUnHzCaigouShenhe2() {
		String sql = "SELECT * FROM quyuHuizongShenhe";
		List<Quyuhuizong> list = (List<Quyuhuizong>) super.query(sql);
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Quyuhuizong> getUnHzCaigouShenhe2ByStaffCode(String staffCode) {
		String hxDbName = ConnectionPool.hxDbName;
		String sql = "SELECT * FROM quyuHuizongShenhe q AND q.orgCode IN (SELECT orgCode FROM " + hxDbName + "..userorg WHERE staffCode = '"
				+ staffCode + "')";
		List<Quyuhuizong> list = (List<Quyuhuizong>) super.query(sql);
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Quyuhuizong> getZongbuHzAlready(String start, String end) {
		String sql = "SELECT * FROM quyuHuizongShenhe WHERE isHz = 1 AND hzDate >= '" + start + "' AND hzDate <='" + end + "'";
		List<Quyuhuizong> list = (List<Quyuhuizong>) super.query(sql);
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Quyuhuizong> getZongbuHzAlreadyByStaffCode(String staffCode, String start, String end) {
		String hxDbName = ConnectionPool.hxDbName;
		String sql = "SELECT * FROM quyuHuizongShenhe q WHERE q.isHz = 1 AND q.hzDate >= '" + start + "' AND q.hzDate <='" + end
				+ "' AND q.orgCode IN (SELECT orgCode FROM " + hxDbName + "..userorg WHERE staffCode = '" + staffCode + "')";
		List<Quyuhuizong> list = (List<Quyuhuizong>) super.query(sql);
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Quyuhuizong> getZongbuHzHavent(String start, String end) {
		String sql = "SELECT * FROM quyuHuizongShenhe2";
		List<Quyuhuizong> list = (List<Quyuhuizong>) super.query(sql);
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Quyuhuizong> getZongbuHzHaventByStaffCode(String staffCode, String start, String end) {
		String hxDbName = ConnectionPool.hxDbName;
		String sql = "SELECT * FROM quyuHuizongShenhe2 q AND q.orgCode IN (SELECT orgCode FROM " + hxDbName + "..userorg WHERE staffCode = '"
				+ staffCode + "')";
		List<Quyuhuizong> list = (List<Quyuhuizong>) super.query(sql);
		return list;
	}

	public String buildHuizong(List<PsForm> list) {
		String result = "";
		System.out.println(list.size());
		return result;
	}

	public String addHuizongShenheBatch(List<Quyuhuizong> list, Set<String> set) {
		String result = "";
		List<String> sqls = new ArrayList<String>();
		for (Quyuhuizong hz : list) {
			String sql = "INSERT INTO quyuHuizongShenhe(billNo,orgCode,pluCode,Color,csize,cup,counts,hzDate,isHz) VALUES('" + hz.getBillNo() + "','"
					+ hz.getOrgCode() + "','" + hz.getPluCode() + "','" + hz.getColor() + "','" + hz.getSize() + "','" + hz.getCup() + "',"
					+ hz.getCounts() + ",getDate(),'0')";
			sqls.add(sql);
		}
		for (String bill : set) {
			String sql = "UPDATE quyuHuizong SET isHz = 1 WHERE billNo = '" + bill + "'";
			sqls.add(sql);
		}
		try {
			executeBatch(sqls);
		} catch (DatabaseException e) {
			e.printStackTrace();
			log.error(e.getMessage());
			return e.getMessage();
		}
		return result;
	}

	public String addUnHuizongShenheBatch(List<Quyuhuizong> list, Set<String> set) {
		String result = "";
		List<String> sqls = new ArrayList<String>();
		for (Quyuhuizong hz : list) {
			String sql = "INSERT INTO quyuHuizongShenhe2(billNo,orgCode,pluCode,Color,csize,cup,counts,hzDate,isHz) VALUES('" + hz.getBillNo()
					+ "','" + hz.getOrgCode() + "','" + hz.getPluCode() + "','" + hz.getColor() + "','" + hz.getSize() + "','" + hz.getCup() + "',"
					+ hz.getCounts() + ",getDate(),'0')";
			sqls.add(sql);
		}
		for (String bill : set) {
			String sql = "UPDATE quyuHuizong2 SET isHz = 1 WHERE billNo = '" + bill + "'";
			sqls.add(sql);
		}
		try {
			executeBatch(sqls);
		} catch (DatabaseException e) {
			e.printStackTrace();
			log.error(e.getMessage());
			return e.getMessage();
		}
		return result;
	}

}
